import openpyxl as opx
import os,configparser,logging,time
import pandas as pd

cfg_file = "cfg.ini"

def log_init():
    if not(os.path.exists("LogFile")):
        os.mkdir("LogFile")
        print("create dir")
    if not(os.path.exists("export")):
        os.mkdir("export")
    time_str = time.strftime('%Y_%m%d_%H%M%S', time.localtime())
    log_file_name = ".\\LogFile\\"+time_str+".txt"
    logging.basicConfig(filename=log_file_name,format="%(asctime)s %(name)s:%(levelname)s-->%(message)s",level='DEBUG',encoding='utf-8')

def check_data(df_bom,df_origin):
    logging.info("Check data start...")
    length = df_bom.shape[0]
    for each in df_bom.index:
        logging.info("开始查询存货编码:"+str(each))
        ser = df_bom.loc[each]
        standard = df_origin.loc[df_origin[df_origin["存货编码"]==each].index[0]]
        res_info = True if (ser["描述"]==standard["存货描述"]) else False
        res_name = True if (ser["名称"]==standard["存货名称"]) else False
        res_partno = True if(ser["出厂型号"]==standard["规格型号"]) else False
        res_fac = True if(ser["生产厂家"]==standard["品牌"]) else False
        if not(res_info and res_name and res_partno and res_fac):
            logging.info(each+"--Result:名称:{name}\t描述:{info}\t型号:{no}\t厂家:{fac}".format(info=res_info,name=res_name,no=res_partno,fac=res_fac))

def check_info(df_bom):
    logging.info("Check info start...")
    df_bom = df_bom[df_bom.index==df_bom.index]
    return df_bom

def get_sheet(file_name,key):
    logging.info("File Name:"+file_name)
    wb = opx.load_workbook(file_name)
    sheet_list = []
    for each in wb.sheetnames:
        if each.find(key)>-1:
            sheet_list.append(each)
    logging.info(sheet_list)
    wb.close()
    return sheet_list

def cmp(df_a,df_b,cfg):
    logging.info("df_a:"+str(df_a.shape))
    logging.info("df_b:"+str(df_b.shape))
    logging.info("df_a:"+str(df_a.shape))
    mode = cfg["default"]["mode"]
    logging.info("Command mode:"+mode)
    res_check =[]
    res_check.append(["存货编码","名称","描述","型号","位号","用量"])
    if mode=="compare":
        la = df_a.shape[0]
        lb = df_b.shape[0]
        if la != lb:
            logging.info("长度不一致...")
        for each in df_a.index:
            sera = df_a.loc[each]
            if each in df_b.index:
                serb = df_b.loc[each]
                res_name = True if sera["名称"]==serb["名称"] else False
                res_des = True if sera["描述"]==serb["描述"] else False
                res_PN = True if sera["出厂型号"]==serb["出厂型号"] else False
                res_dev = True if sera["位号"]==serb["位号"] else False
                res_num = True if sera["用量"]==serb["用量"] else False
                result = res_name and res_des and res_PN and res_dev and res_num
                logging.info("存货编码:%s"%each)
                logging.info("名称:a-->%s\tb-->%s"%(sera["名称"],serb["名称"]))
                logging.info("描述:a-->%s\tb-->%s"%(sera["描述"],serb["描述"]))
                logging.info("出厂型号:a-->%s\tb-->%s"%(sera["出厂型号"],serb["出厂型号"]))
                logging.info("位号:a-->%s\tb-->%s"%(sera["位号"],serb["位号"]))
                logging.info("用量:a-->%s\tb-->%s"%(sera["用量"],serb["用量"]))
                if sera["类别"]=="替代料":
                    logging.info("存货编码:{bm}\t名称:{name}\t描述:{des}\t型号:{pn}".format(bm=each,name=res_name,des=res_des,pn=res_PN))
                    res_check.append([each,res_name,res_des,res_PN,])
                else:
                    res_check.append([each,res_name,res_des,res_PN,res_dev,res_num,])
                    logging.info("存货编码:{bm}\t名称:{name}\t描述:{des}\t型号:{pn}\t位号:{dev}\t用量:{num}".format(bm=each,name=res_name,des=res_des,pn=res_PN,dev=res_dev,num=res_num))
            else:
                logging.info("{bm} Fail,在BOM_b中无法找到...".format(bm=each))
    return res_check

if __name__=="__main__":
    log_init()
    cfg = configparser.ConfigParser()
    cfg.read(cfg_file,'utf-8')
    bom_a = cfg["default"]["bom_a"]
    bom_b = cfg["default"]["bom_b"]
    index = cfg["default"]["index_key"]
    logging.info("Read bom_a file:"+bom_a)
    logging.info("Read bom_b file:"+bom_b)
    sheetlist_a = get_sheet(bom_a,cfg["default"]["sheet_key"])
    sheetlist_b = get_sheet(bom_b,cfg["default"]["sheet_key"])
    wb = opx.Workbook()
    ws = wb.active
    if len(sheetlist_a)!=len(sheetlist_b):
        logging.info("BOM文件无法比较...")
    else:
        for each in range(len(sheetlist_a)):
            logging.info("Start compare Sheet:"+sheetlist_a[each]+'---'+sheetlist_b[each])
            logging.info(each)
            df_a = pd.read_excel(bom_a,header=1,sheet_name=sheetlist_a[each],index_col=index,dtype={index:str})
            df_b = pd.read_excel(bom_b,header=1,sheet_name=sheetlist_b[each],index_col=index,dtype={index:str})
            df_a = df_a.query("{index}=={index}".format(index=index))
            res = cmp(df_a,df_b,cfg)
            for each in res:
                ws.append(each)
    time_str = time.strftime('%Y%m%d%H%M%S', time.localtime())
    if not(os.path.exists("Export")):
        os.mkdir("Export")
    file_name = ".\\Export\\对比结果_"+time_str+".xlsx" 
    wb.save(file_name)


